package com.gree.flowable.app.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.gree.flowable.app.pojo.form.CreateTables;
import com.gree.flowable.app.pojo.form.MySQLColumnVO;
import com.gree.flowable.app.pojo.form.OracleColumnVo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.ResultType;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @author ：180849
 * @date ：Created in 2021/4/26 16:19
 * @description：
 * @modified By：
 * @version: 1.0
 */
@Repository
public interface CreateTablesMapper extends BaseMapper<CreateTables> {
    @Select("show tables like #{tableName}")
    String queryTabelName(@Param("tableName") String tableName);

    @Select("select COLUMN_NAME as columnName,COLUMN_COMMENT as columnComment,CHARACTER_MAXIMUM_LENGTH as characterLength,COLUMN_DEFAULT as columnDefault,DATA_TYPE as dataType,COLUMN_KEY as columnKey,IS_NULLABLE as isNullable from information_schema.columns where table_name =#{formId}")
    @ResultType(MySQLColumnVO.class)
    List<MySQLColumnVO> queryColumnsInMySQL(String formId);

    @Select("select COLUMN_NAME as columnName,COLUMN_COMMENT as columnComment,CHARACTER_MAXIMUM_LENGTH as characterLength,COLUMN_DEFAULT as columnDefault,DATA_TYPE as dataType,COLUMN_KEY as columnKey,IS_NULLABLE as isNullable from information_schema.columns where table_name =#{formId} AND column_name = #{column}")
    @ResultType(MySQLColumnVO.class)
    MySQLColumnVO queryColumnsInMySQLByColumn(@Param("formId")String formId,@Param("column")String column);

    @Select("select data_type FROM information_schema.`COLUMNS` WHERE table_name = #{formId} AND column_name = #{column}")
    String queryColumn(@Param("formId")String formId,@Param("column")String column);

    /**
     * 在Oracle数据库查询表名
     * @param tableName     表名
     * @return
     */
    @Select("SELECT ut.TABLE_NAME  FROM USER_TABLES ut WHERE TABLE_NAME = UPPER(#{tableName})")
    String queryTableNameInOracle(@Param("tableName")String tableName);

    @Select("SELECT\n" +
            "\tUTC.table_name,\n" +
            "\tUTC.COLUMN_NAME,\n" +
            "\tUTC.DATA_TYPE,\n" +
            "\tUTC.DATA_LENGTH,\n" +
            "\tUTC.DATA_PRECISION,\n" +
            "\tUTC.DATA_SCALE,\n" +
            "\tUTC.NULLABLE,\n" +
            "\tucc.comments,\n" +
            "\t(SELECT \n" +
            " 'yes'\n" +
            "FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC\n" +
            "WHERE UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME\n" +
            "AND UC.TABLE_NAME = utc.table_name AND UCC.COLUMN_NAME = utc.COLUMN_NAME\n" +
            "AND CONSTRAINT_TYPE = 'P') AS column_key\n" +
            "FROM\n" +
            "\tuser_tab_columns UTC, user_col_comments ucc where  utc.COLUMN_NAME = ucc.COLUMN_NAME\n" +
            "and\n" +
            "\tutc.table_name = #{tableName}\n" +
            "\tAND\n" +
            "\tucc.TABLE_NAME = #{tableName}")
    List<OracleColumnVo> queryColumnInOracle(@Param("tableName")String tableName);
}
